package model.datasource;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;

import javax.swing.plaf.SliderUI;

import org.slf4j.Logger;

import common.Constants;

import model.Lesson;
import model.Student;
import model.impl.LessonImpl;
import model.impl.StudentImpl;

public class DatabaseDataSource implements DataSource
{

	public int createTables()
	{
		try
		{
			Connection conn = DatabaseManager.getInstance().getConnection();
			conn.createStatement().execute("PRAGMA foreign_keys = ON");
			
			int res = conn.createStatement().executeUpdate(SQLStatements.CREATE_LESSON_TABLE);
			assert(res == Constants.OK);
			
			res = conn.createStatement().executeUpdate(SQLStatements.CREATE_STUDENT_TABLE);
			assert(res == Constants.OK);
			
			conn.close();
			return Constants.OK;
		}
		catch(SQLException e)
		{
			e.printStackTrace();
		}	
		return Constants.DB_UPDATE_ERROR;
	}
	@Override
	public Collection<Student> requestAllStudents()
	{
		// TODO Auto-generated method stub
		try
		{
			Connection conn = DatabaseManager.getInstance().getConnection();
			ResultSet rs = conn.createStatement().executeQuery(SQLStatements.REQUEST_STUDENTS);
			Collection<Student> studs = new ArrayList<Student>();
			while(rs.next())
			{
				Student s = new StudentImpl(
						rs.getInt("student_id"),
						rs.getString("student_name"),
						rs.getString("student_lastname"),
						rs.getString("student_nickName"),
						rs.getString("student_email"),
						rs.getString("student_school"),
						rs.getString("student_phoneNumber"),
						rs.getInt("student_ageyear"));
				studs.add(s);
			}
			conn.close();
			return studs;
		}
		catch(SQLException e)
		{
			e.printStackTrace();
		}
		
		return null;
	}

	@Override
	public Collection<Lesson> requestAllLessons()
	{
		// TODO Auto-generated method stub
		Collection<Lesson> lessons = null;
		try
		{
			Connection conn = DatabaseManager.getInstance().getConnection();
			ResultSet rs = conn.createStatement().executeQuery(SQLStatements.REQUEST_LESSONS);
			lessons = new ArrayList<Lesson>();
			while(rs.next())
			{
				Student s = new StudentImpl(rs.getInt("lecture_student_id"), null, null, null, null, null, null, 0);
				Lesson l = new LessonImpl(
						rs.getInt("lecture_id"),
						rs.getString("lecture_title"),
						rs.getString("lecture_content"),
						s,
						rs.getInt("lecture_income"),
						new SimpleDateFormat(Constants.dateFormat).parse(rs.getString("lecture_date")));
				
				lessons.add(l);
			}
			rs.close();
			conn.close();
		}
		catch(SQLException e)
		{
			e.printStackTrace();
		}
		catch(ParseException e)
		{
			e.printStackTrace();
		}
		
		return lessons;

	}

	@Override
	public Lesson addNewLesson(Student s, String title, String content,
			Date date, int income)
	{
		// TODO Auto-generated method stub
		Lesson l = null;
		try
		{
			Connection conn = DatabaseManager.getInstance().getConnection();
			PreparedStatement ps = conn.prepareStatement(SQLStatements.INSERT_NEW_LESSON);
			ps.setInt(1, s.getId());
			ps.setString(2, title);
			ps.setString(3, content);
			ps.setString(4, new SimpleDateFormat(Constants.dateFormat).format(date));
			ps.setInt(5, income);
			
			int res = ps.executeUpdate();
			int id = ps.getGeneratedKeys().getInt(1);
			l = new LessonImpl(id, title, content, s, income, date);
			
			ps.close();
			conn.close();
		}
		catch(SQLException e)
		{
			e.printStackTrace();
		}
		
		return l;
	}

	@Override
	public Student addNewStudent(String name, String lastName, String nickName,
			String email, String school, String phoneNumber, int year)
	{
		// TODO Auto-generated method stub
		Student s = null;
		try
		{
			Connection conn = DatabaseManager.getInstance().getConnection();
			PreparedStatement ps = conn.prepareStatement(SQLStatements.INSERT_NEW_STUDENT);
			
			ps.setString(1, name);
			ps.setString(2, lastName);
			ps.setString(3, nickName);
			ps.setString(4, school);
			ps.setString(5, phoneNumber);
			ps.setString(6, email);
			ps.setInt(7, year);
			
			int res = ps.executeUpdate();

			int id = ps.getGeneratedKeys().getInt(1);
			s = new StudentImpl(id, name, lastName, nickName, email, school, phoneNumber, year);
			
			conn.close();
		}
		catch(SQLException e)
		{
			e.printStackTrace();
		}
		
		return s;

	}

	@Override
	public int deleteStudent(int id)
	{
		// TODO Auto-generated method stub
		int res = 0;
		try
		{
			Connection conn = DatabaseManager.getInstance().getConnection();
			PreparedStatement ps = conn.prepareStatement(SQLStatements.DELETE_STUDENT);
			
			ps.setInt(1, id);
			res = ps.executeUpdate();
			
			ps = conn.prepareStatement(SQLStatements.DELETE_STUDENT_LESSONS);
			ps.setInt(1, id);
			res = ps.executeUpdate();
			
			conn.close();
		}
		catch(SQLException e)
		{
			e.printStackTrace();
		}
		
		return res;

	}

	@Override
	public int deleteLesson(int id)
	{
		// TODO Auto-generated method stub
		int res = 0;
		try
		{
			Connection conn = DatabaseManager.getInstance().getConnection();
			
			PreparedStatement ps = conn.prepareStatement(SQLStatements.DELETE_LESSON);
			ps.setInt(1, id);
			res = ps.executeUpdate();
			
			ps.close();
			conn.close();
		}
		catch(SQLException e)
		{
			e.printStackTrace();
		}

		return res;
	}

	@Override
	public Student updateStudent(int id, String name, String lastName,
			String nickName, String email, String school, String phoneNumber,
			int year)
	{
		// TODO Auto-generated method stub
		Student s = null;
		try
		{
			Connection conn = DatabaseManager.getInstance().getConnection();
			PreparedStatement ps = conn.prepareStatement(SQLStatements.UPDATE_STUDENT);
			ps.setString(1, name);
			ps.setString(2, lastName);
			ps.setString(3, nickName);
			ps.setString(4, email);
			ps.setString(5, school);
			ps.setString(6, phoneNumber);
			ps.setInt(7, year);
			ps.setInt(8, id);
			
			int res = ps.executeUpdate();
		
			s = new StudentImpl(id, name, lastName, nickName, email, school, phoneNumber, year);
			
			ps.close();
			conn.close();
		}
		catch(SQLException e)
		{
			e.printStackTrace();
		}
		
		return s;

	}

	@Override
	public Lesson updateLesson(int id, Student s, String title, String content,
			Date date, int income)
	{
		// TODO Auto-generated method stub
		Lesson l = null;
		try
		{
			Connection conn = DatabaseManager.getInstance().getConnection();
			PreparedStatement ps = conn.prepareStatement(SQLStatements.UPDATE_LESSON);
			
			ps.setInt(1, s.getId());
			ps.setString(2, title);
			ps.setString(3, content);
			ps.setString(4, new SimpleDateFormat(Constants.dateFormat).format(date));
			ps.setInt(5, income);
			
			int res = ps.executeUpdate();
			
			if(res == Constants.OK)
			{
				l = new LessonImpl(id, title, content, s, income, date);
			}
			
			ps.close();
			conn.close();
		}
		catch(SQLException e)
		{
			e.printStackTrace();
		}
		
		return l;

	}
	
}
